package org.pager.dao.impl;

import org.pager.dao.UserInfoDao;
import org.pager.entity.UserInfo;
import org.pager.utils.DbUtils;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.List;

/**
 * @author zqx
 * @date 2024-06-07
 */
public class UserInfoDaoImpl implements UserInfoDao {

    @Override
    public List<UserInfo> selectAll() {
        return List.of();
    }

    @Override
    public List<UserInfo> selectByPage(int currentPage, int pageSize) {

        List<UserInfo> list = new ArrayList<>();

        // 第一：定义要操作数据库的 SQL 语句
        StringBuffer sql = new StringBuffer();
        sql.append("select * from (");
        sql.append("select *, row_number() over(order by a.id asc) as rownumber ");
        sql.append("from userinfo as a");
        sql.append(") as b ");
        sql.append("where rownumber between  ? and ?");

        // 第二：执行查询操作
        DbUtils dbUtils = new DbUtils();
        int from = (currentPage - 1) * pageSize + 1;
        int to = currentPage * pageSize;

        try {
            ResultSet rst = dbUtils.executeQuery(sql.toString(), from, to);

            // 第三：遍历结果集，读取各行各列的数据，封装到实体对象，添加到List集合
            while (rst.next()) {
                // 1.读取各行各列的数据
                int userId = rst.getInt(1);
                String username = rst.getString(2);
                String password = rst.getString(3);
                String name = rst.getString(4);
                String image = rst.getString(5);
                String sex = rst.getString(6);
                String phone = rst.getString(7);
                String address = rst.getString(8);
                String zipCode = rst.getString(9);
                String email = rst.getString(10);
                Timestamp regTime = rst.getTimestamp(11);

                // 2.封装到实体对象
                UserInfo userInfo = new UserInfo();
                userInfo.setId(userId);
                userInfo.setUsername(username);
                userInfo.setPassword(password);
                userInfo.setName(name);
                userInfo.setImage(image);
                userInfo.setSex(sex);
                userInfo.setPhone(phone);
                userInfo.setAddress(address);
                userInfo.setZipCode(zipCode);
                userInfo.setEmail(email);
                userInfo.setRegTime(regTime);

                // 3.添加到List集合
                list.add(userInfo);
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }

        // 第四：返回
        return list;
    }

    @Override
    public long selectCount() {
        long r = 0;
        // 第一：定义要操作数据库SQL语句
        String sql = "select count(id) from userinfo";

        // 第二：执行SQL语句
        DbUtils dbUtils = new DbUtils();
        try {
            ResultSet rst = dbUtils.executeQuery(sql);

            if (rst.next()) {
                r = rst.getInt(1);
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
        // 第三：返回结果
        return r;
    }

    @Override
    public List<UserInfo> selectByPage(int currentPage, int pageSize, String cond) {
        List<UserInfo> list = new ArrayList<>();

        // 第一：定义要操作数据库的 SQL 语句
        StringBuffer sql = new StringBuffer();
        sql.append("select * from (");
        sql.append("select *, row_number() over(order by a.id asc) as rownumber ");
        sql.append("from userinfo as a ");
        sql.append("where username like ?");
        sql.append(") as b ");
        sql.append("where rownumber between ? and ?");

        // 第二：执行查询操作
        DbUtils dbUtils = new DbUtils();
        int from = (currentPage - 1) * pageSize + 1;
        int to = currentPage * pageSize;

        try {
            ResultSet rst = dbUtils.executeQuery(sql.toString(), "%" + cond + "%", from, to);

            // 第三：遍历结果集，读取各行各列的数据，封装到实体对象，添加到List集合
            while (rst.next()) {
                // 1.读取各行各列的数据
                int userId = rst.getInt(1);
                String username = rst.getString(2);
                String password = rst.getString(3);
                String name = rst.getString(4);
                String image = rst.getString(5);
                String sex = rst.getString(6);
                String phone = rst.getString(7);
                String address = rst.getString(8);
                String zipCode = rst.getString(9);
                String email = rst.getString(10);
                Timestamp regTime = rst.getTimestamp(11);

                // 2.封装到实体对象
                UserInfo userInfo = new UserInfo();
                userInfo.setId(userId);
                userInfo.setUsername(username);
                userInfo.setPassword(password);
                userInfo.setName(name);
                userInfo.setImage(image);
                userInfo.setSex(sex);
                userInfo.setPhone(phone);
                userInfo.setAddress(address);
                userInfo.setZipCode(zipCode);
                userInfo.setEmail(email);
                userInfo.setRegTime(regTime);

                // 3.添加到List集合
                list.add(userInfo);
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }

        // 第四：返回
        return list;
    }

    @Override
    public long selectCount(String cond) {
        long r = 0;
        // 第一：定义要操作数据库SQL语句
        String sql = "select count(id) from userinfo where username like ?";

        // 第二：执行SQL语句
        DbUtils dbUtils = new DbUtils();
        try {
            ResultSet rst = dbUtils.executeQuery(sql,"%"+cond+"%");

            if (rst.next()) {
                r = rst.getInt(1);
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
        // 第三：返回结果
        return r;
    }
}
